﻿unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, RzPanel, RzSplit,
  cxGraphics, cxControls, cxLookAndFeels, cxLookAndFeelPainters, dxCore,
  dxCoreClasses, dxHashUtils, dxSpreadSheetCore, dxSpreadSheetPrinting,
  dxSpreadSheetFormulas, dxSpreadSheetFunctions, dxSpreadSheetGraphics,
  dxSpreadSheetClasses, dxSpreadSheetTypes, dxSpreadSheet, Vcl.Grids, AdvObj,
  BaseGrid, AdvGrid, Vcl.StdCtrls, RzEdit, FormEx_View, System.ImageList,
  Vcl.ImgList, RzButton, RzRadChk, RzTabs,qjson, dxSpreadSheetCoreHistory,
  dxSpreadSheetConditionalFormatting, dxSpreadSheetConditionalFormattingRules,
  dxSpreadSheetContainers, dxSpreadSheetHyperlinks, dxSpreadSheetUtils;

type
  TForm1 = class(TFormExView)
    RzSplitter1: TRzSplitter;
    Grid_Data: TAdvStringGrid;
    Excl_Data: TdxSpreadSheet;
    RzSplitter2: TRzSplitter;
    Tool_1: TRzToolbar;
    Btnx_View: TRzToolButton;
    Btnx_Copy: TRzToolButton;
    Btnx_Quit: TRzToolButton;
    il1: TImageList;
    dlgOpen1: TOpenDialog;
    RzSplitter3: TRzSplitter;
    Chkb_Unicod: TRzCheckBox;
    Page_1: TRzPageControl;
    TabSheet1: TRzTabSheet;
    TabSheet2: TRzTabSheet;
    Memo_CODE: TRzMemo;
    Memo_JSON: TRzMemo;
    Chkb_zStart: TRzCheckBox;
    Btnv_Root: TRzToolButton;
    procedure Btnx_QuitClick(Sender: TObject);
    procedure Btnx_ViewClick(Sender: TObject);
    procedure Btnx_CopyClick(Sender: TObject);
    procedure Btnv_RootClick(Sender: TObject);
  private
  protected
    procedure SetInitialize;override;
    procedure SetCommParams;override;
    procedure SetGridParams;override;
    procedure SetComboItems;override;
    procedure TryFreeAndNil;override;
  public
    procedure SetGridColParams(AAdvGrid:TAdvStringGrid;AColParams:TStringList;AFontColor:TColor=clBlack);
  end;

var
  Form1: TForm1;

implementation

uses
  PerlRegEx,Class_KzUtils,Class_OutLook,Helpr_UniEngine,Class_Element,Class_FakeData,
  Class_Display,Class_Variabl,Class_UiUtils;

{$R *.dfm}


procedure TForm1.Btnv_RootClick(Sender: TObject);
begin
  TKzUtils.Explore;
end;

procedure TForm1.Btnx_CopyClick(Sender: TObject);
var
  I:Integer;
  M:Integer;
  S:string;
  Stat:Boolean;
  Item:TdxSpreadSheetMergedCell;
  Cell:TdxSpreadSheetCell;

  List:TCollection;
  OutC:TOutLook;
begin
  try
    List:=TCollection.Create(TOutLook);

    with Excl_Data do
    begin
      Memo_CODE.Lines.Clear;

      with ActiveSheetAsTable.Selection.Area do
      begin
        Grid_Data.RemoveRows(1,Grid_Data.RowCount-1);
        Grid_Data.RemoveCols(1,Grid_Data.ColCount-1);

        Grid_Data.ColCount:=ActiveSheetAsTable.Selection.Area.Width+2;
        Grid_Data.RowCount:=ActiveSheetAsTable.Selection.Area.Height+2;
        for I := Left to Right do
        begin
          for M := Top to Bottom do
          begin
            Cell:=ActiveSheetAsTable.Cells[M,I];
            if Cell=nil then Continue;
            Grid_Data.Cells[I+1,M+1]:=Trim(Cell.DisplayText);
          end;
        end;
      end;

      with ActiveSheetAsTable.MergedCells do
      begin
        for I := 0 to Count-1 do
        begin
          Item:=Items[I];
          if Item=nil then Continue;
          Grid_Data.MergeCells(Item.Area.Left+1,Item.Area.Top+1,Item.Area.Width+1,Item.Area.Height+1);
          Grid_Data.Alignments[Item.Area.Left+1,Item.Area.Top+1] := taCenter;
        end;
      end;

      with Grid_Data do
      begin
        for I := 1 to ColCount-1 do
        begin
          for M := 1 to RowCount-1 do
          begin
            Stat:=False;
            if IsMergedCell(I,M) then
            begin
              if IsBaseCell(I,M) then
              begin
                Stat:=True;
              end;
            end else
            begin
              Stat:=True;
            end;

            if Stat then
            begin
              if Chkb_zStart.Checked then
              begin
                Memo_CODE.Lines.Add(Format('Add(''%D,%D,%D,%D,%S'');',[I,M-1,CellProperties[I,M].CellSpanX+1,CellProperties[I,M].CellSpanY+1,TKzUtils.IfThen(Chkb_Unicod.Checked,TKzUtils.jsencode(Trim(Cells[I,M])),Trim(Cells[I,M]))]));
              end else
              begin
                Memo_CODE.Lines.Add(Format('Add(''%D,%D,%D,%D,%S'');',[I,M,  CellProperties[I,M].CellSpanX+1,CellProperties[I,M].CellSpanY+1,TKzUtils.IfThen(Chkb_Unicod.Checked,TKzUtils.jsencode(Trim(Cells[I,M])),Trim(Cells[I,M]))]));
              end;

              OutC := TOutLook(List.Add);
              OutC.X := I;
              OutC.Y := M;
              OutC.W := CellProperties[I, M].CellSpanX + 1;
              OutC.H := CellProperties[I, M].CellSpanY + 1;
              OutC.T := Trim(Cells[I, M]);
            end;
          end;
        end;

        Memo_JSON.Lines.Add(TOutLook.ToJSON(List));
        //@TOutLook.ToFILE(toName('d:\output\listcell%s.json'),List,Chkb_Format.Checked);
      end;
    end;
  finally
    if List<>nil then TKzUtils.TryFreeAndNil(List);
  end;
end;

procedure TForm1.Btnx_QuitClick(Sender: TObject);
begin
  Application.Terminate;
end;

procedure TForm1.Btnx_ViewClick(Sender: TObject);
var
  OD:TOpenDialog;
begin
  try
    OD:=TOpenDialog.Create(nil);
    OD.Filter:='xls|*.xls|xlsx|*.xlsx';
    if OD.Execute then
    begin
      Excl_Data.LoadFromFile(OD.FileName);
    end;
  finally
    FreeAndNil(OD);
  end;
end;

procedure TForm1.SetComboItems;
begin
  inherited;

end;

procedure TForm1.SetCommParams;
begin
  inherited;
  Caption := 'XlsToGrid';

  Font.Name := '微软雅黑';
  Font.Size := 10;

  RzSplitter1.Percent := 50;
  RzSplitter2.Percent := 50;
  RzSplitter3.LowerRight.Visible := False;

  Btnx_Quit.Caption := '取消';
  Btnx_View.Caption := '打开';
  Btnx_Copy.Caption := '转换';
end;

procedure TForm1.SetGridColParams(AAdvGrid: TAdvStringGrid;
  AColParams: TStringList; AFontColor: TColor);
var
  I,A,B,C,D:Integer;
  RegEx:TPerlRegEx;
  ListP:TStringList;
begin
  if AColParams.Count=0 then raise Exception.Create('参数未指定.');

  RegEx:=TPerlRegEx.Create;
  ListP:=TStringList.Create;
  try
    for I:=0 to AColParams.Count-1 do
    begin
      with AAdvGrid do
      begin
        ListP.Clear;
        RegEx.Subject:=AColParams.Strings[I];
        RegEx.RegEx:=',';
        RegEx.Split(ListP,MaxInt);

        A:=StrToInt(Trim(ListP.Strings[0]));
        B:=StrToInt(Trim(ListP.Strings[1]));
        C:=StrToInt(Trim(ListP.Strings[2]));
        D:=StrToInt(Trim(ListP.Strings[3]));

        MergeCells(A,B,C,D);
        Cells[A,B]:=Trim(ListP.Strings[4]);
        FontStyles[A,B]:=[fsBold];
        FontColors[A,B]:=AFontColor;
        Alignments[A,B]:=taCenter;
      end;
    end;
  finally
    FreeAndNil(RegEx);
    FreeAndNil(ListP);
  end;
end;

procedure TForm1.SetGridParams;
begin
  inherited;
  Grid_Data.ColCount:=2;
  Grid_Data.RowCount:=2;
end;

procedure TForm1.SetInitialize;
begin
  inherited;

end;

procedure TForm1.TryFreeAndNil;
begin
  inherited;

end;

end.
